﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

public partial class QuarterPlan_ImportQuarterPlan : System.Web.UI.Page
{
    string path = HttpContext.Current.Server.MapPath("~/") + @"Upload\Excel\";
    int QuarterDate = Utility.HttpHelp.getParamsInt("QuarterDate");
    string Sponsor = Utility.HttpHelp.getParamsString("Sponsor");
    int VerifyCircuitId = Utility.HttpHelp.getParamsInt("VerifyCircuitId");
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
        }
    }

    DataTable GetExcelTable2007(string path)
    {
        //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
        FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);

        IWorkbook workbook = WorkbookFactory.Create(file);
        //获取excel的第一个sheet
        XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0);

        DataTable table = new DataTable();
        //获取sheet的首行
        XSSFRow headerRow = (XSSFRow)sheet.GetRow(0);
        //一行最后一个方格的编号 即总的列数
        int cellCount = headerRow.LastCellNum;

        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }
        //最后一列的标号  即总的行数
        int rowCount = sheet.LastRowNum;

        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            XSSFRow row = (XSSFRow)sheet.GetRow(i);
            DataRow dataRow = table.NewRow();

            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                {
                    if (row.Cells[j].CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                    {
                        if (HSSFDateUtil.IsCellDateFormatted(row.Cells[j]))
                        {
                            dataRow[j] = row.Cells[j].DateCellValue;
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    else
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }
            }

            table.Rows.Add(dataRow);
        }

        workbook = null;
        sheet = null;
        StringBuilder str = new StringBuilder("<table>");
        str.Append("<tr>");
        for (int i = 0; i < table.Columns.Count; i++)
        {
            str.Append("<td>" + table.Columns[i].ColumnName + "</td>");
        }
        str.Append("</tr>");
        for (int i = 0; i < table.Rows.Count; i++)
        {
            str.Append("<tr>");
            for (int j = 0; j < table.Columns.Count; j++)
            {
                str.Append("<td>" + table.Rows[i][j].ToString() + "</td>");
            }
            str.Append("</tr>");
        }
        str.Append("</table>");
        return table;
    }


    DataTable GetExcelTable2003(string path)
    {
        //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
        FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);

        IWorkbook workbook = WorkbookFactory.Create(file);
        //获取excel的第一个sheet
        HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0);

        DataTable table = new DataTable();
        //获取sheet的首行
        HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
        //一行最后一个方格的编号 即总的列数
        int cellCount = headerRow.LastCellNum;

        for (int i = headerRow.FirstCellNum; i < cellCount; i++)
        {
            DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
            table.Columns.Add(column);
        }
        //最后一列的标号  即总的行数
        int rowCount = sheet.LastRowNum;

        for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(i);
            DataRow dataRow = table.NewRow();

            for (int j = row.FirstCellNum; j < cellCount; j++)
            {
                if (row.GetCell(j) != null)
                {
                    if (row.Cells[j].CellType == NPOI.SS.UserModel.CellType.NUMERIC)
                    {
                        if (HSSFDateUtil.IsCellDateFormatted(row.Cells[j]))
                        {
                            dataRow[j] = row.Cells[j].DateCellValue;
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    else
                    {
                        dataRow[j] = row.GetCell(j).ToString();
                    }
                }
            }

            table.Rows.Add(dataRow);
        }

        workbook = null;
        sheet = null;
        StringBuilder str = new StringBuilder("<table>");
        str.Append("<tr>");
        for (int i = 0; i < table.Columns.Count; i++)
        {
            str.Append("<td>" + table.Columns[i].ColumnName + "</td>");
        }
        str.Append("</tr>");
        for (int i = 0; i < table.Rows.Count; i++)
        {
            str.Append("<tr>");
            for (int j = 0; j < table.Columns.Count; j++)
            {
                str.Append("<td>" + table.Rows[i][j].ToString() + "</td>");
            }
            str.Append("</tr>");
        }
        str.Append("</table>");
        return table;
    }


    /// <summary>
    /// 读取Excel内容
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnImport_Click(object sender, EventArgs e)
    {

        if (fupExcelPath.HasFile)
        {
            string _filename = this.fupExcelPath.FileName;
            //判断目录是否存在
            if (System.IO.Directory.Exists(path))
            {
                //保存
                fupExcelPath.SaveAs(path + _filename);
            }
            else
            {
                System.IO.Directory.CreateDirectory(path);
                //保存
                fupExcelPath.SaveAs(path + _filename);
            }
            string ExcelPath = path + _filename;

            try
            {
                DataTable dt = new DataTable();
                if (ExcelPath.Contains("xlsx"))
                {
                    dt = GetExcelTable2007(ExcelPath);

                }
                else
                {
                    dt = GetExcelTable2003(ExcelPath);
                }
                ImportQuarterPlan(dt);
            }
            catch (Exception ex)
            {

            }
        }
    }


    /// <summary>
    /// 导入年计划
    /// </summary>
    void ImportQuarterPlan(DataTable dt)
    {
        Model.UserInfo objUserInfo = new Model.UserInfo();
        SqlParameter[] QuareterResolveParameters = null;
        SqlParameter[] QuareterParameters = null;
        int QuarterPlanId = 0;

        using (SqlConnection conn = new SqlConnection(DAL.SqlHelper.connStr))
        {
            conn.Open();
            SqlTransaction trans = conn.BeginTransaction();
            try
            {

                //遍历Table
                foreach (DataRow dr in dt.Rows)
                {
                    //判断是否为分解计划
                    if (GetStrCount(dr["计划编号"].ToString()))
                    {
                        //QuarterPlanId = Convert.ToInt32(dr["序号"].ToString().Substring(0, dr["序号"].ToString().IndexOf(".")));
                        QuareterResolveParameters = new SqlParameter[15];
                        QuareterResolveParameters[0] = new SqlParameter("@StartTime", Convert.ToDateTime(dr["起始时间"]));
                        QuareterResolveParameters[1] = new SqlParameter("@CompleteTime", Convert.ToDateTime(dr["完成时间"]));
                        QuareterResolveParameters[2] = new SqlParameter("@QuarterPlanId", QuarterPlanId);
                        QuareterResolveParameters[3] = new SqlParameter("@QuarterDetailPlanId", dr["计划编号"].ToString());
                        QuareterResolveParameters[4] = new SqlParameter("@WorkItem", dr["计划名称"].ToString());
                        QuareterResolveParameters[5] = new SqlParameter("@MasterDepartment", dr["责任部门"].ToString());
                        QuareterResolveParameters[6] = new SqlParameter("@MasterPeople", dr["责任人"].ToString());
                        QuareterResolveParameters[7] = new SqlParameter("@SlaveDepartment", FilterToString(dr["配合部门"].ToString().Replace("，", ",")));
                        QuareterResolveParameters[8] = new SqlParameter("@SlaveDepartmentDuty", dr["配合部门责任"] == null ? "" : dr["配合部门责任"].ToString());
                        QuareterResolveParameters[9] = new SqlParameter("@EvaluatePeople", dr["评估人"] == null ? "" : dr["评估人"].ToString());
                        QuareterResolveParameters[10] = new SqlParameter("@PlanSendState", Convert.ToInt32("3"));
                        QuareterResolveParameters[11] = new SqlParameter("@Remart", dr["备注"] == null ? "" : dr["备注"].ToString());
                        QuareterResolveParameters[12] = new SqlParameter("@QuarterDate", QuarterDate);
                        QuareterResolveParameters[13] = new SqlParameter("@VerifyCircuitId", VerifyCircuitId);
                        QuareterResolveParameters[14] = new SqlParameter("@Sponsor", GetSponsor(dr["责任人"].ToString()));//"admin");
                        DAL.SqlHelper.ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "Insert_QuarterPlanDetail", QuareterResolveParameters);
                    }
                    else
                    {
                        if (QuarterPlanId > 0)
                        {
                            SqlParameter[] params1 = new SqlParameter[3];
                            params1[0] = new SqlParameter("@QuarterPlanId", QuarterPlanId);
                            params1[1] = new SqlParameter("@UserName", objUserInfo.UserName);
                            params1[2] = new SqlParameter("@VerifyPeopleOpinion", "");
                            //执行上一循环的年计划分解
                            DAL.SqlHelper.ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "Update_QuarterPlanPlanSendStateByEvaluatePeopleForImport", params1);
                        }
                        QuareterParameters = new SqlParameter[17];
                        QuareterParameters[0] = new SqlParameter("@StartTime", Convert.ToDateTime(dr["起始时间"]));
                        QuareterParameters[1] = new SqlParameter("@CompleteTime", Convert.ToDateTime(dr["完成时间"]));
                        QuareterParameters[2] = new SqlParameter("@WorkItem", dr["计划名称"].ToString());
                        QuareterParameters[3] = new SqlParameter("@MasterDepartment", dr["责任部门"].ToString());
                        QuareterParameters[4] = new SqlParameter("@MasterPeople", dr["责任人"].ToString());
                        QuareterParameters[5] = new SqlParameter("@SlaveDepartment", FilterToString(dr["配合部门"].ToString().Replace("，", ",")));
                        QuareterParameters[6] = new SqlParameter("@SlaveDepartmentDuty", dr["配合部门责任"] == null ? "" : dr["配合部门责任"].ToString());
                        QuareterParameters[7] = new SqlParameter("@PlanSendState", Convert.ToInt32("3"));
                        QuareterParameters[8] = new SqlParameter("@IsConsensus", Convert.ToInt32("0"));
                        QuareterParameters[9] = new SqlParameter("@Remart", dr["备注"] == null ? "" : dr["备注"].ToString());
                        QuareterParameters[10] = new SqlParameter("@QuarterDate", QuarterDate);
                        QuareterParameters[11] = new SqlParameter("@VerifyCircuitId", VerifyCircuitId);
                        QuareterParameters[12] = new SqlParameter("@Sponsor", GetSponsor(dr["责任人"].ToString()));//"admin");
                        QuareterParameters[13] = new SqlParameter("@EvaluatePeople", dr["评估人"]);
                        QuareterParameters[14] = new SqlParameter("@QuarterPlanSortId", Convert.IsDBNull(dr["计划编号"]) == true ? 1 : dr["计划编号"]);
                        QuareterParameters[15] = new SqlParameter("@QuarterSource", "");
                        QuareterParameters[16] = new SqlParameter("@StrategicPlanId", Convert.ToInt32(dr["战略计划Id"]));
                        DAL.SqlHelper.ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "Insert_QuarterPlanWithExcel", QuareterParameters);
                        QuarterPlanId = Convert.ToInt32(DAL.SqlHelper.ExecuteScalar(conn, trans, CommandType.Text, "select top 1 QuarterPlanId from DS_QuarterPlan order by QuarterPlanId desc"));


                    }
                }
                if (QuarterPlanId > 0)
                {
                    SqlParameter[] params1 = new SqlParameter[3];
                    params1[0] = new SqlParameter("@QuarterPlanId", QuarterPlanId);
                    params1[1] = new SqlParameter("@UserName", objUserInfo.UserName);
                    params1[2] = new SqlParameter("@VerifyPeopleOpinion", "");
                    //执行上一循环的年计划分解
                    DAL.SqlHelper.ExecuteNonQuery(conn, trans, CommandType.StoredProcedure, "Update_QuarterPlanPlanSendStateByEvaluatePeopleForImport", params1);
                }
                trans.Commit();
                Utility.HttpHelp.alert("导入成功", this.Page);
            }
            catch (System.Exception ex)
            {
                trans.Rollback();
                Utility.HttpHelp.alert(ex.Message, this.Page);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }

    /// <summary>
    /// 替换空格顿号为,号
    /// </summary>
    /// <param name="strSource"></param>
    /// <returns></returns>
    string FilterPlace(string strSource)
    {
        if (strSource.Contains(" ") || strSource.Contains("、"))
        {
            strSource = strSource.Replace(" ", "");
            strSource = strSource.Replace("、", "");
            FilterPlace(strSource);
        }
        return strSource;
    }


    /// <summary>
    /// 过滤重复,号返回字符串
    /// </summary>
    /// <param name="strSource"></param>
    /// <returns></returns>
    string FilterToString(string strSource)
    {
        strSource = FilterPlace(strSource);
        return strSource;
        //string SlaveDepartmentSource = GetSlaveDepartment();
        //string[] result = SlaveDepartmentSource.Split(',');
        //StringBuilder strResult = new StringBuilder();

        //foreach (string var in result)
        //{
        //    if (strSource.Contains(var))
        //    {
        //        if (var.Trim().Length != 0)
        //        {
        //            strResult.Append(var + ",");
        //        }
        //    }
        //}
        //if (strResult.ToString().Length > 0)
        //{
        //    strResult = strResult.Remove(strResult.Length - 1, 1);
        //}
        //return strResult.ToString();
    }

    bool GetStrCount(string str)
    {
        string[] result = str.Split('.');
        if (result.Length == 3)
        {
            return true;
        }
        else
        {
            return false;
        }
    }

    /// <summary>
    /// 获取所有的配合部门名单
    /// </summary>
    /// <returns></returns>
    string GetSlaveDepartment()
    {
        StringBuilder str = new StringBuilder();
        string sql = "select RealName from DS_UserInfo";
        DataTable dt = DAL.SqlHelper.ExecuteDataset(DAL.SqlHelper.connStr, CommandType.Text, sql).Tables[0];
        foreach (DataRow dr in dt.Rows)
        {
            str.Append(dr["RealName"] + ",");
        }
        str.Append("全部部门" + "," + "独立完成" + "," + "关联部门" + "," + "卢志建," + "全体部门,");
        sql = "select DepartmentName from DS_DepartmentInfo";
        dt = DAL.SqlHelper.ExecuteDataset(DAL.SqlHelper.connStr, CommandType.Text, sql).Tables[0];
        foreach (DataRow dr in dt.Rows)
        {
            str.Append(dr["DepartmentName"] + ",");
        }
        return str.ToString();
    }

    /// <summary>
    /// 根据责任人获取发起人用户名
    /// </summary>
    /// <param name="masterPeople"></param>
    /// <returns></returns>
    string GetSponsor(string masterPeople)
    {
        string sql = "select username from ds_userinfo where isdelete=0 and isdimission=0 and  realname='" + masterPeople + "'";
        DataTable dt = DAL.SqlHelper.ExecuteDataset(DAL.SqlHelper.connStr, CommandType.Text, sql).Tables[0];
        foreach (DataRow dr in dt.Rows)
        {
            if (dr["username"].ToString().Length > 0)
            {
                return dr["username"].ToString();
            }
            else
            {
                return "";
            }
        }
        return "";
    }

}
